﻿using System;
using System.Data.OleDb;
using System.IO;
using System.Data;
using System.Text;
using System.Web;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace LdExpress
{
    public enum FieldTypes
    {
        Text = 0,
        //        Integer = 1,
        //        Currency = 2,
        DropDown = 3,
        Radio = 4,
        CheckBox = 5,
        Image = 6,
        TextArea = 7,
        File = 8,
        Date = 9,
        Time = 10,
        Password = 11
    }

    /// <summary>
    /// Summary description for DBModule.
    /// </summary>

    public static class GV
    {
        public static MySqlConnection cn;
        public static MySqlTransaction trans;
        public static HttpApplicationState gApplication
        {
            get
            {
                return HttpContext.Current.Application;
            }
        }
        public static string ErrorMessage;
        public static HttpResponse Response
        {
            get
            {
                return HttpContext.Current.Response;
            }
        }

    }

    public class DBModule
    {
        public const string Access = "MS Access";
        public const string SQLServer = "SQLServer";
        public const string Oracle = "Oracle";
        public const string mySQL = "mySQL";
        public static string strConnString = "";
        public static string DbType;

        public DBModule()
        {
        }
        public static string PathConfig = "";
        public static void SetDBConnectionString(string FileFullPath)
        {
            if (PathConfig == "") PathConfig = FileFullPath;
        }

        public static MySqlConnection CreateDBConnection()
        {
            return CreateDBConnection(true);
        }
        public static void BuildDatabaseParameters()
        {
            BuildDatabaseParameters(false);
        }
        public static void BuildDatabaseParameters(bool bRebuild)
        {

            string configKeyConnectionString = "MySqlConnectionString";
            string configKeyDatabaseType = "DBType";
            if ((strConnString == "") || (bRebuild == true))
            {
                //throw new Exception("test" + ConfigurationManager.AppSettings[configKeyConnectionString].ToString());
                if (ConfigurationManager.AppSettings[configKeyConnectionString] != null)
                {
                    strConnString = ConfigurationManager.AppSettings[configKeyConnectionString].ToString();

                    DbType = DBModule.mySQL;
                    if (ConfigurationManager.AppSettings[configKeyDatabaseType] != null)
                    {
                        DbType = ConfigurationManager.AppSettings[configKeyDatabaseType].ToString();
                    }
                    //throw new Exception("test" + DbType);
                }
                else
                {
                    string strServerName, strDatabase, strUserName, strPassword;
                    ReadDatabaseInfo(out strServerName, out strDatabase, out strUserName, out strPassword, out DbType);

                    switch (DbType)
                    {
                        case DBModule.Access:
                            strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathConfig + "\\" + strDatabase;
                            break;
                        case DBModule.SQLServer:
                            strConnString = "driver={SQL Server};server=" + strServerName + ";uid=" + strUserName + ";password=" + strPassword + "; database=" + strDatabase + ";Provider=SQLOLEDB;";
                            break;
                        case DBModule.Oracle:
                            break;
                        case DBModule.mySQL:
                            strConnString = "Data Source=" + strServerName + ";Database=" + strDatabase + ";uid=" + strUserName + ";pwd=" + strPassword;
                            break;
                    }
                }
                //throw new Exception("test=" + strConnString);
                #region test database connection
                try
                {
                    //throw new Exception(strConnString);
                    MySqlConnection cn = new MySqlConnection(strConnString);
                    //MySqlConnection cn = new MySqlConnection();
                    //cn.ConnectionString = "Provider=MySqlProv;Data Source=localhost;User id=root;Password=";
                    //MySqlConnection cn = new MySqlConnection("server=localhost; user id=root; password=; database=ldtechdefault; pooling=false;provider=MySql.Data.MySqlClient;");
                    //MySqlConnection cn = new MySqlConnection(strConnString);
                    cn.Open();
                    cn.Close();
                    cn = null;
                }
                catch (Exception ex)
                {
                    strConnString = "";
                    throw ex;
                }
                #endregion
            }
        }
        public static MySqlConnection CreateDBConnection(bool doOpen)
        {
            if (strConnString == "")
            {
                BuildDatabaseParameters();
            }

            //strConnString = "server=localhost;database=aseanexpress;uid=root;pwd=root";
            //throw new Exception("sd=" + strConnString);
            //MySqlConnection cn = new MySqlConnection(strConnString);
            MySqlConnection cn = new MySqlConnection(strConnString);
            // if we cannot open the connection, set the connection string to empty so that we will 
            // rebuild the connection string in next time 
            //throw new Exception("test" +  cn.Database.ToString());
            try
            {
                if (doOpen) cn.Open();
            }
            catch (Exception ex)
            {
                strConnString = "";
                throw ex;
            }
            return cn;
        }
        public static void CloseDBConnection(MySqlConnection cn)
        {
            if (cn != null)
            {
                if (cn.State == ConnectionState.Open) cn.Close();
            }
        }

        #region ExecuteNonQuery functions
        //Execute non-query command, return number of rows affected
        public static int ExecuteNonQuery(string strCommand, MySqlConnection cn, MySqlTransaction trans)
        {
            int retVal = 0;
            bool isOpen = false;
            bool isNull = false;
            //string strConnString = "server=localhost;database=aseanexpress;uid=root;pwd=root";
            if (cn == null)
            {
                isNull = true;
                cn = CreateDBConnection();
            }
            else
            {
                isOpen = (cn.State == ConnectionState.Open);
                if (!isOpen) cn.Open();
            }
            //cn = CreateDBConnection();
            //cn = new MySqlConnection("server=localhost;database=aseanexpress;uid=root;pwd=root");
            //cn = CreateDBConnection();
            //cn.Open();
            //throw new Exception("sd=" + strConnString);
            //MySqlConnection cn = new MySqlConnection(strConnString);
            //MySqlConnection conn = new MySqlConnection(strConnString);
            //conn.Open();
            //try
            //{
            MySqlCommand cmd = new MySqlCommand(strCommand);
            //cmd.CommandType = mySQL;
            cmd.Connection = cn;
            retVal = cmd.ExecuteNonQuery();

            if (!isOpen)
            {
                CloseDBConnection(cn);
                if (isNull)
                {
                    cn.Dispose();
                    cn = null;
                }
            }
            //}
            //catch (Exception ex)
            //{
            //    if (!isOpen)
            //    {
            //        CloseDBConnection(cn);
            //        if (isNull)
            //        {
            //            cn.Dispose();
            //            cn = null;
            //        }
            //    }
            //    throw new Exception("Error executing query: " + strCommand + " ;<br>" + ex.Message, ex);
            //}
            return retVal;
        }
        /*
                public static int ExecuteNonQuery(string strCommand)
                {
                    MySqlConnection cn = CreateDBConnection(true);
                    try
                    {
                        //DebugModule.WriteLine("DBModule.ExecuteNonQuery\n\t{0}", strCommand);
                        MySqlCommand  cmd = new MySqlCommand(strCommand, cn);
                        return cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        CloseDBConnection(ref cn);
                    }
                }
        */
        public static int ExecuteNonQuerySP(MySqlConnection cn, MySqlTransaction trans, string ProcName, params object[] parameters)
        {
            string[] ParamNames = null;
            if (parameters != null && parameters.Length > 0)
            {
                ParamNames = new String[parameters.Length];
                for (int i = 0; i < parameters.Length; i++)
                    ParamNames[i] = "@" + (i + 1);
            }
            return _ExecuteNonQuerySP(cn, trans, ProcName, ParamNames, parameters);
        }

        public static int _ExecuteNonQuerySP(MySqlConnection cn, MySqlTransaction trans, string ProcName, string[] ParamNames, object[] ParamValues)
        {
            bool cnOpen = false;
            bool IsConnTransmited = true;
            if (cn == null)
            {
                IsConnTransmited = false;
                cn = CreateDBConnection();
            }
            else
            {
                cnOpen = (cn.State == ConnectionState.Open);
                if (!cnOpen) cn.Open();
            }

            try
            {

                MySqlCommand cmd = new MySqlCommand(ProcName, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (trans != null) cmd.Transaction = trans;

                if (ParamNames != null)
                    for (int i = 0; i < ParamNames.Length; i++)
                        cmd.Parameters.AddWithValue(ParamNames[i], ParamValues[i]);
                //cmd.Parameters.Add(ParamNames[i], ParamValues[i]);

                //				DebugModule.WriteLine("DBModule._ExecuteSP, ProcName={0}\n\tParameters: {{{1}}}", ProcName, GeneralModules.SqlParamsToString(cmd.Parameters));
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!IsConnTransmited)
                {
                    if (!cnOpen) cn.Close();
                }
                else { }
            }
        }
        #endregion

        #region ExecuteQuery, ExecuteReader functions
        public static DataSet ExecuteQuery(string strSQL, MySqlConnection cn, MySqlTransaction trans)
        {
            return ExecuteQuery(strSQL, "", cn, trans);
        }
        public static DataSet ExecuteQuery(string sqlCommand, string strTableName, MySqlConnection cn, MySqlTransaction trans)
        {
            DataSet ds = new DataSet();
            bool isOpen = false;
            bool isNull = false;
            if (cn == null)
            {
                isNull = true;
                cn = CreateDBConnection();
            }
            else
            {
                isOpen = (cn.State == ConnectionState.Open);
                if (!isOpen) cn.Open();
            }
            try
            {

                MySqlDataAdapter adapter = new MySqlDataAdapter(new MySqlCommand(sqlCommand, cn, trans));
                adapter.SelectCommand.CommandTimeout = 600;
                if (strTableName != "")
                    adapter.Fill(ds, strTableName);
                else
                    adapter.Fill(ds);

                if (!isOpen)
                {
                    CloseDBConnection(cn);
                    if (isNull)
                    {
                        cn.Dispose();
                        cn = null;
                    }
                }
            }
            catch (Exception ex)
            {
                if (!isOpen)
                {
                    CloseDBConnection(cn);
                    if (isNull)
                    {
                        cn.Dispose();
                        cn = null;
                    }
                }
                throw new Exception("Error executing query: " + sqlCommand + ";<br>" + ex.Message, ex);
            }
            return ds;
        }
        private static int ValidateLicenseNumber(string strLicenseNumber)
        {
            //			StreamReader f = File.OpenText(PathConfig + "\\database.cfg");
            //			string s;
            //			string [] spl;
            //
            //			while (null != (s = f.ReadLine()))
            //			{
            //				spl = s.Split('=');
            //				switch (spl[0].Trim().ToLower()) 
            //				{
            //					case "servername": strServerName = spl[1]; break;
            //					case "username": strUserName = spl[1]; break;
            //					case "password": strPassword = spl[1]; break;
            //					case "dbname": strDatabase = spl[1]; break;
            //					case "dbtype": strDatabaseType = spl[1];break;
            //				}
            //			}
            //			f.Close();
            return 1;
        }
        /// <summary>
        /// Execute a query and return a result as a string
        /// If no result, return null
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="cn"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public static string ExecuteQueryForOneResult(string strSQL, MySqlConnection cn, MySqlTransaction trans)
        {
            string strResult = null;
            DataSet ds = ExecuteQuery(strSQL, cn, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                    strResult = ds.Tables[0].Rows[0][0].ToString();
            }
            return strResult;
        }
        public static string ExecuteQueryForListValue(string strSQL, MySqlConnection cn, MySqlTransaction trans)
        {
            string strResult = null;
            DataSet ds = ExecuteQuery(strSQL, cn, trans);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (strResult != null)
                        strResult += "," + ds.Tables[0].Rows[i][0].ToString();
                    else
                        strResult = ds.Tables[0].Rows[i][0].ToString(); ;
                }
            }
            return strResult;
        }
        public static DataSet ExecuteQuery(string strSQL, int NumOfItemsPerPage, int ItemPage, MySqlConnection cn, MySqlTransaction trans)
        {
            return ExecuteQuery(strSQL, "", NumOfItemsPerPage, ItemPage, cn, trans);
        }
        public static DataSet ExecuteQuery(string sqlCommand, string strTableName, int NumOfItemsPerPage, int ItemPage, MySqlConnection cn, MySqlTransaction trans)
        {
            bool isNull = false;
            bool isOpen = false;
            if (cn == null)
            {
                isNull = true;
                cn = CreateDBConnection();
            }
            else
            {
                isOpen = (cn.State == ConnectionState.Open);
                if (!isOpen) cn.Open();
            }
            DataSet ds = new DataSet();
            DataTable dt;
            if (strTableName != "")
                dt = ds.Tables.Add(strTableName);
            else
                dt = ds.Tables.Add();
            try
            {
                MySqlCommand cmd = new MySqlCommand(sqlCommand, cn, trans);
                MySqlDataReader dReader = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                if (dReader.HasRows)
                {
                    if (ItemPage <= 0) ItemPage = 1;
                    int nPageCount = 0;
                    int nStartRow = NumOfItemsPerPage * (ItemPage - 1) + 1;
                    while (dReader.Read())
                    {
                        nPageCount++;
                        if (nPageCount == nStartRow)
                        {
                            //create column headers
                            int nFieldCount = dReader.FieldCount;
                            for (int i = 0; i < nFieldCount; i++)
                            {
                                dt.Columns.Add(dReader.GetName(i), dReader.GetFieldType(i));
                            }
                            //read data
                            int nPageRow = 0;
                            bool bRead = true;
                            object[] objValues;
                            while ((nPageRow < NumOfItemsPerPage) && bRead)
                            {
                                nPageRow++;
                                objValues = new object[nFieldCount];
                                dReader.GetValues(objValues);
                                dt.Rows.Add(objValues);
                                bRead = dReader.Read();
                            }
                            // break the reading
                            break;
                        }
                    }
                }
                dReader.Close();
                cmd.Dispose();

                if (!isOpen)
                {
                    CloseDBConnection(cn);
                    if (isNull)
                    {
                        cn.Dispose();
                        cn = null;
                    }
                }
            }
            catch (Exception ex)
            {
                if (!isOpen)
                {
                    //if (trans != null) trans.Rollback();
                    CloseDBConnection(cn);
                    if (isNull)
                    {
                        cn.Dispose();
                        cn = null;
                    }
                }
                throw new Exception("Error executing query: " + sqlCommand + ";<br>" + ex.Message, ex);
            }
            return ds;
        }
        public static DataSet ExecuteQuery(string strSQL, int NumOfItemsPerPage, int ItemPage, out int TotalNumOfPages, MySqlConnection cn, MySqlTransaction trans)
        {
            return ExecuteQuery(strSQL, "", NumOfItemsPerPage, ItemPage, out TotalNumOfPages, cn, trans);
        }

        public static DataSet ExecuteQuery(string strSQL, int NumOfItemsPerPage, int ItemPage, out int TotalNumOfPages, out int nTotalItem, MySqlConnection cn, MySqlTransaction trans)
        {
            return ExecuteQuery(strSQL, "", NumOfItemsPerPage, ItemPage, out TotalNumOfPages, out nTotalItem, cn, trans);
        }

        public static DataSet ExecuteQuery(string strSQL, string strTableName, int NumOfItemsPerPage, int ItemPage, out int TotalNumOfPages, MySqlConnection cn, MySqlTransaction trans)
        {
            int nTotalItem = 0;
            return ExecuteQuery(strSQL, strTableName, NumOfItemsPerPage, ItemPage, out TotalNumOfPages, out nTotalItem, cn, trans);
        }

        public static void GetPagingParams(string strSQL, int NumOfItemsPerPage, out int TotalNumOfPages, out int TotalItems, MySqlConnection cn, MySqlTransaction trans)
        {
            string strTotalItems = "";
            int nOrderByPosition = -1;
            TotalNumOfPages = 1;
            TotalItems = 0;

            nOrderByPosition = strSQL.ToLower().LastIndexOf("order by");
            if (nOrderByPosition > 0)
                strTotalItems = strSQL.Substring(0, nOrderByPosition);
            else
                strTotalItems = strSQL;

            strTotalItems = "Select count(*) From (" + strTotalItems + ") as LDTRESTableCount";

            DataSet ds = DBModule.ExecuteQuery(strTotalItems, cn, trans);
            TotalItems = int.Parse(ds.Tables[0].Rows[0][0].ToString());

            if (NumOfItemsPerPage > 0)
            {
                TotalNumOfPages = (int)((TotalItems + NumOfItemsPerPage - 1) / NumOfItemsPerPage);
            }
        }

        public static DataSet ExecuteQuery(string strSQL, string strTableName, int NumOfItemsPerPage, int ItemPage, out int TotalNumOfPages, out int TotalItems, MySqlConnection cn, MySqlTransaction trans)
        {
            DataSet retVal;
            //string strTotalItems = "";
            //int nOrderByPosition = -1;
            TotalNumOfPages = 1;
            TotalItems = 0;
            if (NumOfItemsPerPage > 0)
            {
                GetPagingParams(strSQL, NumOfItemsPerPage, out TotalNumOfPages, out TotalItems, cn, trans);
                /*nOrderByPosition = strSQL.ToLower().LastIndexOf("order by");
                if (nOrderByPosition > 0)
                    strTotalItems = strSQL.Substring(0, nOrderByPosition);
                else
                    strTotalItems = strSQL;

                strTotalItems = "Select count(*) From (" + strTotalItems + ") as LDTRESTableCount";

                //"Select (count(*) +" + NumOfItemsPerPage.ToString() + "-1)/" + NumOfItemsPerPage.ToString() +
                //    " From (" + strTotalNumOfPageSQL + ") as LDTRESTableCount";

                DataSet ds = DBModule.ExecuteQuery(strTotalItems, cn, trans);
                TotalItems = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                TotalNumOfPages = (int) ((TotalItems + NumOfItemsPerPage - 1) / NumOfItemsPerPage);*/

                //if (ItemPage > TotalNumOfPages) ItemPage = 1;
                if (ItemPage > TotalNumOfPages) ItemPage = 9999;
                retVal = ExecuteQuery(strSQL, strTableName, NumOfItemsPerPage, ItemPage, cn, trans);
            }
            else
            {
                retVal = ExecuteQuery(strSQL, strTableName, cn, trans);
                TotalItems = int.Parse(retVal.Tables[0].Rows[0][0].ToString());
            }
            return retVal;
        }
        /*
                public static DataSet ExecuteQuery(string strSQL)
                {
                    MySqlConnection cn = CreateDBConnection();
                    try
                    {
                        DataSet ds = new DataSet();
                        MySqlDataAdapter adapter = new MySqlDataAdapter(strSQL, cn);
                        adapter.Fill(ds);
                        //DebugModule.WriteLine("DBModule.ExecuteQuery\n\t{0}", strSQL);
                        return ds;
                    }
                    finally
                    {
                        CloseDBConnection(ref cn);
                    }
                }
        */
        public static MySqlDataReader ExecuteReader(string strSQL, MySqlConnection cn, MySqlTransaction trans, CommandBehavior behavior)
        {
            MySqlDataReader retVal = null;
            bool cnOpen = false;
            if (cn == null) cn = CreateDBConnection();
            else
            {
                cnOpen = (cn.State == ConnectionState.Open);
                if (!cnOpen) cn.Open();
            }
            try
            {
                //if (!cnOpen) trans = cn.BeginTransaction(IsolationLevel.ReadUncommitted);

                MySqlCommand cmd = new MySqlCommand(strSQL, cn, trans);
                //			DebugModule.WriteLine("DBModule.ExecuteReader\n\t{0}", strSQL);
                if (!cnOpen) //in case the input connection is null or not open we implicitly close the connection
                {			//when the data reader is closed
                    retVal = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    retVal = cmd.ExecuteReader(behavior);
                }
                //if (!cnOpen) trans.Commit();
            }
            // we does not need to close the connection in case of !cnOpen. The connection will be closed when the data reader is closed
            finally
            {
                //				if (behavior != CommandBehavior.CloseConnection) // in case of not implicitly close the connection when the data reader is closed
                //				{
                //					if (!cnOpen) CloseDBConnection(ref cn);
                //				}
            }
            return retVal;
        }

        public static MySqlDataReader ExecuteReader(string strSQL, MySqlConnection cn, MySqlTransaction trans)
        {
            return ExecuteReader(strSQL, cn, trans, CommandBehavior.Default);
        }
        #endregion

        #region Helper functions: Refine input, ReadDatabaseInfo, ...
        //SqlEncode: return an Sql-safe string to put into a string variable in an SQL statement
        //Just convert single quote (') to 2 single quotes ('')
        public static string RefineString(string strSource)
        {
            if (strSource == null) return "";
            string result = strSource.Replace("'", "''");
            //DebugModule.WriteLine(string.Format("DBModule.SqlEncode\n\tfrom {0} to {1}", strSource, result));
            return result;
        }
        public static string RefineDatetime(DateTime dtSource)
        {
            string result = dtSource.ToString("MM/dd/yyyy");
            switch (DbType)
            {
                case DBModule.Access:
                    result = "#" + result + "#";
                    break;
                case DBModule.SQLServer:
                    result = "'" + result + "'";
                    break;
                case DBModule.Oracle:
                    break;
                case DBModule.mySQL:
                    result = "'" + dtSource.ToString("yyyy/MM/dd") + "'";
                    break;
                default:
                    result = dtSource.ToString("yyyy/MM/dd");
                    break;

            }
            return result;
        }
        public static string RefineDatetime(DateTime dtSource, bool bAll)
        {
            string result = "'" + dtSource.ToString("yyyy/MM/dd") + "'";
            if (bAll)
            {
                result = dtSource.ToString("MM/dd/yyyy HH:mm:ss");
                switch (DbType)
                {
                    case DBModule.Access:
                        result = "#" + result + "#";
                        break;
                    case DBModule.SQLServer:
                        result = "'" + result + "'";
                        break;
                    case DBModule.Oracle:
                        break;
                    case DBModule.mySQL:
                        result = "'" + dtSource.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                        break;
                    default:
                        result = dtSource.ToString("yyyy-MM-dd HH:mm:ss");
                        break;

                }
            }
            return result;
        }
        public static string RefineDatetime(string strSource)
        {
            string result = strSource;
            switch (DbType)
            {
                case DBModule.Access:
                    result = "#" + result + "#";
                    break;
                case DBModule.SQLServer:
                    result = "'" + result + "'";
                    break;
                case DBModule.Oracle:
                    break;
                case DBModule.mySQL:
                    break;
            }
            return result;
        }
        private static void ReadDatabaseInfo(out string strServerName, out string strDatabase,
            out string strUserName, out string strPassword, out string strDatabaseType)
        {
            strServerName = "DATASERVER\\DATASERVER2000";
            strDatabase = "ProjectManagement";
            strUserName = "sa";
            strPassword = "sa";
            strDatabaseType = DBModule.SQLServer;
            try
            {
                StreamReader f = File.OpenText(PathConfig + "\\database.cfg");
                string s;
                string[] spl;

                while (null != (s = f.ReadLine()))
                {
                    spl = s.Split('=');
                    switch (spl[0].Trim().ToLower())
                    {
                        case "servername": strServerName = spl[1]; break;
                        case "username": strUserName = spl[1]; break;
                        case "password": strPassword = spl[1]; break;
                        case "dbname": strDatabase = spl[1]; break;
                        case "dbtype": strDatabaseType = spl[1]; break;
                    }
                }
                f.Close();
            }
            catch { }

            //DebugModule.WriteLine("DBModule.ReadDatabaseInfo\n\tServerName={0}, Database={1}, User ID={2}, Password={3}",
            //	strServerName, strDatabase, strUserName, strPassword);
        }
        public static long GetNewID(Type objType, string strTableName, MySqlConnection cn, MySqlTransaction trans)
        {
            long lRetVal = 1;
            string strSQL = "Select top 1 NewID from TG_NewID";
            DataSet ds = ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count == 0)
            {
                lRetVal = 1;
                strSQL = "Insert Into TG_NewID(NewID) values(1)";
                ExecuteNonQuery(strSQL, cn, trans);
            }
            else
            {
                lRetVal = long.Parse(ds.Tables[0].Rows[0][0].ToString()) + 1;
                strSQL = "update TG_NewID set NewID=" + lRetVal.ToString();
                ExecuteNonQuery(strSQL, cn, trans);
            }
            if (objType != null)
            {
                strSQL = "Insert Into TG_Objects(ObjectID, TypeFullName, AssemblyQualifiedName) values(" + lRetVal.ToString() + ",'" + objType.FullName + "','" + objType.AssemblyQualifiedName + "')";
                ExecuteNonQuery(strSQL, cn, trans);

                CreateObjectType(objType, strTableName, cn, trans);

            }
            return lRetVal;
        }
        public static void CreateObjectType(Type objType, string strTableName, MySqlConnection cn, MySqlTransaction trans)
        {
            string strClassName = objType.FullName;
            int nLastPlace = objType.FullName.LastIndexOf('.');

            if (nLastPlace >= 0) strClassName = objType.FullName.Substring(nLastPlace + 1);

            string strSQL = "Select top 1 Name from TG_ObjectType where Name=N'" + strClassName + "'";
            DataSet ds = ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count == 0)
            {
                strSQL = "Insert Into TG_ObjectType(Name, TypeFullName, Description, TableName) values(N'" + strClassName + "',N'" + objType.FullName + "',N'" + objType.AssemblyQualifiedName + "',N'" + strTableName + "')";
                ExecuteNonQuery(strSQL, cn, trans);

                // insert basic permission types
                strSQL = "select Max(ID) from TG_ObjectType";
                long lObjectTypeID = long.Parse(DBModule.ExecuteQueryForOneResult(strSQL, cn, trans));

                CreateBasicPermissions(lObjectTypeID, strClassName, cn, trans);
            }
        }
        public static void CreateBasicPermissions(long lObjectTypeID, string strClassName, MySqlConnection cn, MySqlTransaction trans)
        {
            long lID = GetNewID(null, "TG_Permissions", cn, trans);
            string strSQL = "Insert into TG_Permissions([ID],[ObjectTypeID],[Name],[UniqueName],[Description]) " +
                    "values(" + lID.ToString() + "," + lObjectTypeID + ",N'Add',N'" + strClassName + "Add',N'Add " + strClassName + "')";
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);

            lID = GetNewID(null, "TG_Permissions", cn, trans);
            strSQL = "Insert into TG_Permissions([ID],[ObjectTypeID],[Name],[UniqueName],[Description]) " +
            "values(" + lID.ToString() + "," + lObjectTypeID + ",N'Delete',N'" + strClassName + "Delete',N'Add " + strClassName + "')";
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);

            lID = GetNewID(null, "TG_Permissions", cn, trans);
            strSQL = "Insert into TG_Permissions([ID],[ObjectTypeID],[Name],[UniqueName],[Description]) " +
            "values(" + lID.ToString() + "," + lObjectTypeID + ",N'Edit',N'" + strClassName + "Edit',N'Add " + strClassName + "')";
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);

            lID = GetNewID(null, "TG_Permissions", cn, trans);
            strSQL = "Insert into TG_Permissions([ID],[ObjectTypeID],[Name],[UniqueName],[Description]) " +
            "values(" + lID.ToString() + "," + lObjectTypeID + ",N'List',N'" + strClassName + "List',N'Add " + strClassName + "')";
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);

            lID = GetNewID(null, "TG_Permissions", cn, trans);
            strSQL = "Insert into TG_Permissions([ID],[ObjectTypeID],[Name],[UniqueName],[Description]) " +
            "values(" + lID.ToString() + "," + lObjectTypeID + ",N'View',N'" + strClassName + "View',N'Add " + strClassName + "')";
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public static void ReturnID(long lID, MySqlConnection cn, MySqlTransaction trans)
        {
            if (lID > 0)
            {
                string strSQL = "Select top 1 ReturnID from TG_ReturnID where ReturnID=" + lID.ToString();
                DataSet ds = ExecuteQuery(strSQL, cn, trans);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    strSQL = "Insert Into TG_ReturnID(ReturnID) values(" + lID.ToString() + ")";
                    ExecuteNonQuery(strSQL, cn, trans);
                }

                strSQL = "Delete from TG_Objects where ObjectID=" + lID.ToString();
                ExecuteNonQuery(strSQL, cn, trans);
            }
        }
        public static bool isTableExisting(string strTableName, MySqlConnection cn, MySqlTransaction trans)
        {

            bool retVal = true;
            string strSQL = "SELECT * FROM information_schema.tables  WHERE  table_schema ='shipping' AND table_name = '" + strTableName + "'limit 0,1";
            DataSet ds = ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count == 0) retVal = false;
            //throw new Exception("sql="+strSQL+ " <br>count="+ ds.Tables[0].Rows.Count.ToString());
            return retVal;
        }
        public static bool isColumnExisting(string strTableName, string strColumnName, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = true;
            string strSQL = "select * FROM INFORMATION_SCHEMA.COLUMNS where column_name = '" + strColumnName + "' and table_name = '" + strTableName + "'";
            DataSet ds = ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count == 0) retVal = false;
            return retVal;
        }
        public static bool isIndexExisting(string strTableName, string strColumnName, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = false;
            string strSQL = "SELECT  sysindexkeys.ID FROM sysindexkeys,INFORMATION_SCHEMA.COLUMNS " +
                            "WHERE sysindexkeys.id = OBJECT_ID(N'[" + strTableName + "]') " +
                                "AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME ='" + strTableName + "' " +
                                "AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME='" + strColumnName + "' " +
                                "AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION=sysindexkeys.COLID limit 1";
            DataSet ds = ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0) retVal = true;
            return retVal;
        }
        public static bool isStoredProcedureExisting(string strStoredProcedureName, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = false;
            string strSQL = "select name from sysobjects where type = 'P' and name = '" + strStoredProcedureName + "'";
            DataSet ds = ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0) retVal = true;
            return retVal;
        }
        public static string buildQueryPart(string strField, string strValueList, string strFieldType, string strExpType)
        {
            string retVal = "";
            switch (strFieldType.ToLower())
            {
                case "char":
                case "varchar":
                case "text":
                    if (strValueList.IndexOf("like ") >= 0)
                    {
                        strValueList = strValueList.Replace("like ", "like '");
                        strValueList = strValueList + "'";
                        retVal = strField + " " + strValueList;
                    }
                    else
                    {
                        strValueList = strValueList.Replace("'", "''");
                        strValueList = strValueList.Replace(",", "','");
                        strValueList = "'" + strValueList + "'";

                        retVal = strField;
                        if (strValueList.IndexOf(",") > 0)
                        {
                            if ((strExpType == "=") || (strExpType == "in"))
                                retVal += " in (" + strValueList + ")";
                            else
                                retVal += " not in (" + strValueList + ")";
                        }
                        else
                        {
                            retVal += " " + strExpType + " " + strValueList;
                        }
                    }
                    break;
                case "nchar":
                case "nvarchar":
                case "ntext":
                    if (strValueList.IndexOf("like ") >= 0)
                    {
                        strValueList = strValueList.Replace("like ", "like N'");
                        strValueList = strValueList + "'";
                        retVal = strField + " " + strValueList;
                    }
                    else
                    {
                        strValueList = strValueList.Replace("'", "''");
                        strValueList = strValueList.Replace(",", "',N'");
                        strValueList = "N'" + strValueList + "'";

                        retVal = strField;
                        if (strValueList.IndexOf(",") > 0)
                        {
                            if ((strExpType == "=") || (strExpType == "in"))
                                retVal += " in (" + strValueList + ")";
                            else
                                retVal += " not in (" + strValueList + ")";
                        }
                        else
                        {
                            retVal += " " + strExpType + " " + strValueList;
                        }
                    }
                    break;
                case "datetime":
                    strValueList = strValueList.Replace("'", "''");
                    if (strValueList != "")
                    {
                        if (DbType == DBModule.Access)
                        {
                            strValueList = strValueList.Replace(",", "#,#");
                            strValueList = "#" + strValueList + "#";
                        }
                        else if (DbType == DBModule.SQLServer)
                        {
                            strValueList = strValueList.Replace(",", "','");
                            strValueList = "'" + strValueList + "'";
                        }

                        retVal = strField;
                        if (strValueList.IndexOf(",") > 0)
                        {
                            if ((strExpType == "=") || (strExpType == "in"))
                                retVal += " in (" + strValueList + ")";
                            else
                                retVal += " not in (" + strValueList + ")";
                        }
                        else
                        {
                            retVal += strExpType + strValueList;
                        }
                    }
                    break;
                default:
                    if (strValueList != "")
                    {
                        retVal = strField;
                        if (strValueList.IndexOf(",") > 0)
                        {
                            if ((strExpType == "=") || (strExpType == "in"))
                                retVal += " in (" + strValueList + ")";
                            else
                                retVal += " not in (" + strValueList + ")";
                        }
                        else
                        {
                            retVal += strExpType + strValueList;
                        }
                    }
                    break;
            }

            return retVal;
        }
        #endregion

        #region Stored Procedure functions
        public static void GetDatabaseDataType(string strFieldName, string strTableNames, out string strDataType, out string strMaxLength, MySqlConnection cn, MySqlTransaction trans)
        {
            strDataType = "varchar";
            strMaxLength = "";
            string strSQL;
            DataSet ds;
            DataRow dr;
            string strTableName;
            int nPlace = strFieldName.IndexOf('.');
            if (nPlace > 0)
            {
                strFieldName = strFieldName.Substring(nPlace + 1);
                strTableName = strFieldName.Substring(0, nPlace);
                strSQL = "select Data_Type, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS where table_name = '" + strTableName + "'" +
                    " and Column_Name='" + strFieldName + "'";
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dr = ds.Tables[0].Rows[0];
                    strDataType = dr["Data_Type"].ToString();
                    if (!dr.IsNull("character_maximum_length")) strMaxLength = dr["character_maximum_length"].ToString();
                }
            }
            else
            {
                string[] arrTables = strTableNames.Split(new char[] { ',', ';' });
                for (int i = 0; i < arrTables.Length; i++)
                {
                    strTableName = arrTables[i];
                    strSQL = "select Data_Type,character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS where table_name = '" + strTableName + "'" +
                        " and Column_Name='" + strFieldName + "'";
                    ds = DBModule.ExecuteQuery(strSQL, cn, trans);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dr = ds.Tables[0].Rows[0];
                        strDataType = dr["Data_Type"].ToString();
                        if (!dr.IsNull("character_maximum_length")) strMaxLength = dr["character_maximum_length"].ToString();
                    }
                }
            }
        }
        public static string CreateSPSelectScript(string strSPName, string strParamDefinition,
            string strBegin, string strEnd,
            string strSelect, string strFrom, string strWhere,
            string strOrderBy, string strOrderBySort, string strTableNames,
            string strKeyField, MySqlConnection cn, MySqlTransaction trans)
        {

            string strOrderByDataType, strDataTypeMaxLength;
            DBModule.GetDatabaseDataType(strOrderBy, strTableNames, out strOrderByDataType, out strDataTypeMaxLength, cn, trans);

            string retVal = ""; // "IF EXISTS (SELECT name FROM   sysobjects " +
            //"WHERE  name = N'" + strSPName + "' AND type = 'P') " +
            //"DROP PROCEDURE " + strSPName + " \n\r";
            retVal += "CREATE PROCEDURE " + strSPName + "\n\r";
            if (strParamDefinition.Length > 0) retVal += strParamDefinition + "\n\r";
            retVal += "AS\n\r";
            retVal += "BEGIN\n\r";
            retVal += strBegin + "\n\r";
            retVal += "declare @startRow int, @endRow int, @rowCount int, @pageCount int\n\r";
            retVal += "set @startRow = @pageSize * (@pageNumber - 1)\n\r";
            retVal += "set @endRow = @pageSize * @pageNumber\n\r";
            // calculate exactly the total number of records and the number of pages
            retVal += "select @rowCount = count(*)\n\r";
            retVal += "from " + strFrom + "\n\r";
            if (strWhere.Length > 0) retVal += "where " + strWhere + "\n\r";

            retVal += "declare @dCount as decimal(8,2)\n\r";
            retVal += "Set @dCount = @rowCount\n\r";
            retVal += "if(@dCount > 0 and @pageSize > 0) set @pageCount = ceiling(@dCount / @pageSize)\n\r";
            retVal += "else set @pageCount = 0\n\r";
            retVal += "declare @startingValue " + strOrderByDataType;
            if (strDataTypeMaxLength.Length > 0) retVal += "(" + strDataTypeMaxLength + ")";
            retVal += "\n\r";
            retVal += "declare @startingID int\n\r";
            // work out the starting values
            retVal += "if(@startRow > 0) set rowcount @startRow\n\r";
            retVal += "else set rowcount 1\n\r";
            // skip first rows
            retVal += "select @startingValue = " + strOrderBy + ", @startingID = " + strKeyField + "\n\r";
            retVal += "from " + strFrom + "\n\r";
            if (strWhere.Length > 0) retVal += "where " + strWhere + "\n\r";
            retVal += "order by " + strOrderBy + " " + strOrderBySort + ", " + strKeyField + " " + strOrderBySort + "\n\r";
            // get the page rows
            retVal += "if (@endRow > @rowCount) set @endRow = @rowCount\n\r";
            retVal += "set @endRow = @endRow - @startRow \n\r";
            retVal += "if(@endRow > 0) set rowcount @endRow\n\r";
            retVal += "else set rowcount 0\n\r";

            retVal += "select " + strSelect + "\n\r";
            retVal += "from " + strFrom + "\n\r";
            retVal += "where \n\r";
            retVal += "(@startRow = 0 OR (" + strOrderBy + " = @startingValue And " + strKeyField + " > @startingID) OR " + strOrderBy + " > @startingValue ) \n\r";
            if (strWhere.Length > 0) retVal += " and " + strWhere + "\n\r";
            retVal += "order by " + strOrderBy + " " + strOrderBySort + ", " + strKeyField + " " + strOrderBySort + "\n\r";

            // also return row count and page count
            retVal += "select @rowCount as [RowCount], @pageCount as [PageCount], @startRow + 1 as StartRow, @endRow as EndRow\n\r";

            retVal += strEnd + "\n\r";
            retVal += "END\n\r";
            return retVal;
        }
        public static void CreateSelectStoredProcedure(string strSPName, string strParamDefinition,
            string strBegin, string strEnd,
            string strSelect, string strFrom, string strWhere,
            string strOrderBy, string strOrderBySort, string strTableNames,
            string strKeyField, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = CreateSPSelectScript(strSPName, strParamDefinition, strBegin, strEnd, strSelect, strFrom, strWhere,
                strOrderBy, strOrderBySort, strTableNames, strKeyField, cn, trans);
            //GV.gResponse.Write(strSQL + "<br>");
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public static DataSet ExecuteQuerySP(string strSQL,
            int NumOfItemsPerPage, int ItemPage, out int TotalNumOfPages, out int TotalItems,
            MySqlConnection cn, MySqlTransaction trans)
        {
            TotalNumOfPages = 0;
            TotalItems = 0;

            strSQL += "," + ItemPage + "," + NumOfItemsPerPage;
            DataSet ds = new DataSet();
            bool cnOpen = false;
            if (cn == null) cn = CreateDBConnection();
            else
            {
                cnOpen = (cn.State == ConnectionState.Open);
                if (!cnOpen) cn.Open();
            }
            try
            {
                //if (!cnOpen) trans = cn.BeginTransaction(IsolationLevel.ReadUncommitted);

                MySqlDataAdapter adapter = new MySqlDataAdapter(new MySqlCommand(strSQL, cn, trans));
                adapter.Fill(ds);

                TotalNumOfPages = int.Parse(ds.Tables[1].Rows[0]["PageCount"].ToString());
                TotalItems = int.Parse(ds.Tables[1].Rows[0]["RowCount"].ToString());


                //if (!cnOpen) trans.Commit();
            }
            finally
            {
                if (!cnOpen) CloseDBConnection(cn);
            }
            return ds;
        }
        #endregion
        public static void Debug(string strConditions)
        {
            string strPath = CheckPath("\\log") + "\\log.txt";

            System.IO.FileStream specify = new System.IO.FileStream(strPath, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
            System.IO.StreamWriter File = new System.IO.StreamWriter(specify, Encoding.UTF8);
            File.WriteLine(strConditions);
            File.Close();
            specify.Close();

        }
        public static string CheckPath(string strFolderName)
        {
            string strPath = System.Web.HttpContext.Current.Server.MapPath(".\\") + strFolderName;
            if (!System.IO.Directory.Exists(strPath))
            {
                try
                {
                    System.IO.Directory.CreateDirectory(strPath);
                }
                catch
                {
                    //    GV.gData["ErrorCode"] = "Space_ASSET_DIR_CREATE";
                    //    GV.gData["ErrorMessage"] = "Cannot create space assets directory, please check permission! " + strPath;
                    strPath = "";
                }
            }
            return strPath;
        }
        public static void ImportDefaultData(string strInputFolder, MySqlConnection cn, MySqlTransaction trans)
        {
            //System.Web.HttpContext.Current.Response.Write("1." + strInputFolder + "<br>");
            if (!Directory.Exists(strInputFolder)) return;

            //System.Web.HttpContext.Current.Response.Write("2." + strInputFolder + "<br>");

            DirectoryInfo di = new DirectoryInfo(strInputFolder);
            FileInfo[] arrFileInfo = di.GetFiles("*.sql");

            //string strLogFile = AppDomain.CurrentDomain.BaseDirectory + "\\ImportLog.txt";
            //StreamWriter sw = new StreamWriter(strLogFile, true, System.Text.Encoding.UTF8);

            foreach (FileInfo fi in arrFileInfo)
            {
                //sw.WriteLine("Importing file: " + fi.Name + ": " + System.DateTime.Now);

                //System.Web.HttpContext.Current.Response.Write("3." + fi.FullName + "<br>");

                StreamReader sr = new StreamReader(fi.FullName, Encoding.Unicode);
                //int nCount = 0;
                //int nTotal = 0;
                string strSQL = "";
                string strContent = sr.ReadToEnd();

                sr.Close();

                bool bSetIdentityInsert = false;
                string strTableName = "";
                int nPlace = strContent.IndexOf("SET IDENTITY_INSERT");
                int nPlaceOfOn = 0;

                if (nPlace > 0)
                {
                    bSetIdentityInsert = true;
                    nPlaceOfOn = strContent.IndexOf(" ON", nPlace);
                    if (nPlaceOfOn > 0) strTableName = strContent.Substring(nPlace + 19, nPlaceOfOn - nPlace - 19).Trim();
                }

                int nPlaceSetOFF = -1;
                if (bSetIdentityInsert && strTableName.Length > 0)
                {
                    strSQL = "SET IDENTITY_INSERT " + strTableName + " ON";
                    nPlaceSetOFF = strContent.IndexOf("SET IDENTITY_INSERT " + strTableName + " OFF");
                }


                /* BEGIN INSERT ****************************************/
                nPlace = strContent.IndexOf("INSERT ", nPlaceOfOn);
                while (nPlace >= 0)
                {
                    int nNextPlace = strContent.IndexOf("INSERT ", nPlace + 1);
                    if ((nNextPlace < 0) || (nPlaceSetOFF > 0 && nNextPlace > nPlaceSetOFF))
                    {
                        int nLastClose = strContent.LastIndexOf(")");
                        if (nLastClose > 0)
                        {
                            strSQL = strContent.Substring(nPlace, nLastClose - nPlace + 1);
                            if (bSetIdentityInsert && strTableName.Length > 0)
                            {
                                strSQL = "SET IDENTITY_INSERT " + strTableName + " ON;" + strSQL + "; SET IDENTITY_INSERT " + strTableName + " OFF";
                            }
                            DBModule.ExecuteNonQuery(strSQL, cn, trans);
                        }
                        nPlace = -1;
                    }
                    else
                    {
                        strSQL = strContent.Substring(nPlace, nNextPlace - nPlace).Trim();
                        if (strSQL.EndsWith("GO"))
                        {
                            strSQL = strSQL.Substring(0, strSQL.Length - 2).Trim();
                        }
                        if (bSetIdentityInsert && strTableName.Length > 0)
                        {
                            strSQL = "SET IDENTITY_INSERT " + strTableName + " ON;" + strSQL + "; SET IDENTITY_INSERT " + strTableName + " OFF";
                        }
                        DBModule.ExecuteNonQuery(strSQL, cn, trans);

                        nPlace = nNextPlace;
                    }
                }

                /* END INSERT ******************************************/


                if (bSetIdentityInsert && strTableName.Length > 0)
                {
                    strSQL = "SET IDENTITY_INSERT " + strTableName + " OFF";
                }

                //sw.WriteLine("Total: " + nTotal + " records: " + System.DateTime.Now);

            }

            //sw.Close();
        }

        public static string GetFieldValue(long ID, string fielName, string tableName, MySqlConnection cn, MySqlTransaction trans)
        {
            string retVal = "";
            if (ID > 0)
            {
                string sql = "Select " + fielName + " from " + tableName + " where ID=" + ID.ToString() + " limit 0,1";
                DataSet ds = DBModule.ExecuteQuery(sql, cn, trans);
                if (ds.Tables[0].Rows.Count > 0)
                    retVal = ds.Tables[0].Rows[0][fielName].ToString();
            }
            return retVal;
        }
        public static string GetFieldValue(string strCode, string fielName, string tableName, MySqlConnection cn, MySqlTransaction trans)
        {
            string retVal = "";
            if (strCode.Length > 0)
            {
                string sql = "Select " + fielName + " from " + tableName + " where Code='" + strCode.ToString() + "' limit 0,1";
                DataSet ds = DBModule.ExecuteQuery(sql, cn, trans);
                if (ds.Tables[0].Rows.Count > 0)
                    retVal = ds.Tables[0].Rows[0][fielName].ToString();
            }
            return retVal;
        }
        public static string UnicodeToPlain(string strEncode)
        {
            string oStr = null;
            int p1 = 0;
            int p2 = 0;
            p2 = strEncode.Length;
            p1 = 0;
            oStr = "";
            while (p1 < p2)
            {
                switch (strEncode.Substring(p1, 1))
                {
                    case "à":
                    case "á":
                    case "ạ":
                    case "ả":
                    case "ã":
                    case "ă":
                    case "ằ":
                    case "ắ":
                    case "ẳ":
                    case "ặ":
                    case "ẵ":
                    case "â":
                    case "ầ":
                    case "ấ":
                    case "ẩ":
                    case "ẫ":
                    case "ậ":
                        oStr += "a";
                        break;
                    case "À":
                    case "Á":
                    case "Ạ":
                    case "Ả":
                    case "Ã":
                    case "Ă":
                    case "Ằ":
                    case "Ắ":
                    case "Ẳ":
                    case "Ặ":
                    case "Ẵ":
                    case "Â":
                    case "Ầ":
                    case "Ấ":
                    case "Ẩ":
                    case "Ẫ":
                    case "Ậ":
                        oStr += "A";
                        break;
                    case "è":
                    case "é":
                    case "ẹ":
                    case "ẻ":
                    case "ẽ":
                    case "ê":
                    case "ề":
                    case "ế":
                    case "ể":
                    case "ệ":
                    case "ễ":
                        oStr += "e";
                        break;
                    case "È":
                    case "É":
                    case "Ẹ":
                    case "Ẻ":
                    case "Ẽ":
                    case "Ê":
                    case "Ề":
                    case "Ế":
                    case "Ể":
                    case "Ệ":
                    case "Ễ":
                        oStr += "E";
                        break;
                    case "ò":
                    case "ó":
                    case "ọ":
                    case "ỏ":
                    case "õ":
                    case "ơ":
                    case "ờ":
                    case "ớ":
                    case "ở":
                    case "ợ":
                    case "ỡ":
                    case "ô":
                    case "ồ":
                    case "ố":
                    case "ổ":
                    case "ộ":
                    case "ỗ":
                        oStr += "o";
                        break;
                    case "Ò":
                    case "Ó":
                    case "Ọ":
                    case "Ỏ":
                    case "Õ":
                    case "Ơ":
                    case "Ờ":
                    case "Ớ":
                    case "Ở":
                    case "Ợ":
                    case "Ỡ":
                    case "Ô":
                    case "Ồ":
                    case "Ố":
                    case "Ổ":
                    case "Ộ":
                    case "Ỗ":
                        oStr += "O";
                        break;
                    case "ù":
                    case "ú":
                    case "ụ":
                    case "ủ":
                    case "ũ":
                    case "ư":
                    case "ừ":
                    case "ứ":
                    case "ử":
                    case "ự":
                    case "ữ":
                        oStr += "u";
                        break;
                    case "Ù":
                    case "Ú":
                    case "Ụ":
                    case "Ủ":
                    case "Ũ":
                    case "Ư":
                    case "Ừ":
                    case "Ứ":
                    case "Ử":
                    case "Ự":
                    case "Ữ":
                        oStr += "U";
                        break;
                    case "ỳ":
                    case "ý":
                    case "ỵ":
                    case "ỷ":
                    case "ỹ":
                        oStr += "y";
                        break;
                    case "Ỳ":
                    case "Ý":
                    case "Ỵ":
                    case "Ỷ":
                    case "Ỹ":
                        oStr += "Y";
                        break;
                    case "ì":
                    case "í":
                    case "ị":
                    case "ỉ":
                    case "ĩ":
                        oStr += "i";
                        break;
                    case "Ì":
                    case "Í":
                    case "Ị":
                    case "Ỉ":
                    case "Ĩ":
                        oStr += "I";
                        break;
                    case "đ":
                        oStr += "d";
                        break;
                    case "Đ":
                        oStr += "D";
                        break;
                    default:
                        oStr += strEncode.Substring(p1, 1);
                        break;
                }
                p1 = p1 + 1;
            }
            return oStr;
        }
        public static string ucFirstAllWords(string str)
        {
            //string retVal = "";
            //string[] pieces = str.Split(' ');
            //for (int i = 0; i < pieces.Length; i++)
            //{
            //    string temp = pieces[i].ToLower();
            //    string j = temp.Substring(0, 1).ToUpper();
            //    j += temp.Substring(1);
            //    retVal += " " + j;
            //}
            //return retVal;
            return System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(str.ToLower());
        }
    }
}